/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.ssm.changeunit.persistance;

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

/**
 *
 * @author 시리우스
 */
public class ChangeUnitItemDAO extends AbstractChangeUnitItemDAO {

    @Override
    public ChangeUnitItemDTO find(String changeUnitItemId) throws PersistanceException {

        ChangeUnitItemDTO dto = null;
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select CHANGEUNITITEMID, CHANGEUNITID, FILEPATH, "
                +"FIRSTPARENTCHANGEUNITID, FIRSTPARENTCHANGEUNITITEMID, "
                +"SECONDPARENTCHANGEUNITID, SECONDPARENTCHANGEUNITITEMID, REFCHANGELOGID "
                +" from APP.CHANGEUNITITEM where CHANGEUNITITEMID = '"+changeUnitItemId+"'";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            if(rs.next()) {
                dto = new ChangeUnitItemDTO();
                dto.setChangeUnitItemId(rs.getString("CHANGEUNITITEMID"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));
                dto.setFilePath(rs.getString("FILEPATH"));
                dto.setFirstParentChangeUnitId(rs.getString("FIRSTPARENTCHANGEUNITID"));
                dto.setFirstParentChangeUnitItemId(rs.getString("FIRSTPARENTCHANGEUNITITEMID"));
                dto.setSecondParentChangeUnitId(rs.getString("SECONDPARENTCHANGEUNITID"));
                dto.setSecondParentChangeUnitItemId(rs.getString("SECONDPARENTCHANGEUNITITEMID"));
                dto.setRefChangeLogId(rs.getString("REFCHANGELOGID"));
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ChangeUnitItemDAO.find: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dto; 
    }

    @Override
    public void insert(ChangeUnitItemDTO changeUnitItemDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
//        String sql="insert into APP.CHANGEUNITITEM (CHANGEUNITITEMID, CHANGEUNITID, FILEPATH, FIRSTPARENTCHANGEUNITID, FIRSTPARENTCHANGEUNITITEMID, "
//                +" SECONDPARENTCHANGEUNITID, SECONDPARENTCHANGEUNITITEMID, REFCHANGELOGID ) " 
//                + "values ('"+changeUnitItemDTO.getChangeUnitItemId()+"','"+changeUnitItemDTO.getChangeUnitId()+"','"
//                +changeUnitItemDTO.getFilePath()+"','"+changeUnitItemDTO.getFirstParentChangeUnitId() +"','"
//                +changeUnitItemDTO.getFirstParentChangeUnitItemId()+"','"+changeUnitItemDTO.getSecondParentChangeUnitId()+"','"
//                +changeUnitItemDTO.getSecondParentChangeUnitItemId()+"','"+changeUnitItemDTO.getRefChangeLogId() +"')";
        
        String sql="insert into APP.CHANGEUNITITEM (CHANGEUNITITEMID, CHANGEUNITID, FILEPATH, FIRSTPARENTCHANGEUNITID, FIRSTPARENTCHANGEUNITITEMID, "
                +" SECONDPARENTCHANGEUNITID, SECONDPARENTCHANGEUNITITEMID, REFCHANGELOGID ) " 
                + "values (?,?,?,?,?,?,?,?)";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql); 
            
            stmt.setString(1, changeUnitItemDTO.getChangeUnitItemId());
            stmt.setString(2, changeUnitItemDTO.getChangeUnitId());
            stmt.setString(3, changeUnitItemDTO.getFilePath());
            stmt.setString(4, changeUnitItemDTO.getFirstParentChangeUnitId());
            stmt.setString(5, changeUnitItemDTO.getFirstParentChangeUnitItemId());
            stmt.setString(6, changeUnitItemDTO.getSecondParentChangeUnitId());
            stmt.setString(7, changeUnitItemDTO.getSecondParentChangeUnitItemId());
            stmt.setString(8, changeUnitItemDTO.getRefChangeLogId());
            
            stmt.executeUpdate(); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChangeUnitItemDAO.insert: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }
    }

    @Override
    public void update(ChangeUnitItemDTO changeUnitItemDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
//        String sql="update APP.CHANGEUNITITEM set "
//                + "CHANGEUNITITEMID= '"+ changeUnitItemDTO.getChangeUnitItemId() + "' ,"
//                + "CHANGEUNITID= '"+ changeUnitItemDTO.getChangeUnitId() + "' ,"
//                + "FILEPATH= '"+ changeUnitItemDTO.getFilePath() + "' ,"
//                + "FIRSTPARENTCHANGEUNITID= '"+ changeUnitItemDTO.getFirstParentChangeUnitId() + "' ,"
//                + "FIRSTPARENTCHANGEUNITITEMID= '"+ changeUnitItemDTO.getFirstParentChangeUnitItemId() + "' ,"
//                + "SECONDPARENTCHANGEUNITID= '"+ changeUnitItemDTO.getSecondParentChangeUnitId() + "' ,"
//                + "SECONDPARENTCHANGEUNITITEMID= '"+ changeUnitItemDTO.getSecondParentChangeUnitItemId() + "' ,"
//                + "REFCHANGELOGID= '"+ changeUnitItemDTO.getRefChangeLogId() + "' "
//                + " where CHANGEUNITITEMID = '"+ changeUnitItemDTO.getChangeUnitItemId() +"'";
        String sql="update APP.CHANGEUNITITEM set "
                + "CHANGEUNITITEMID=?, "
                + "CHANGEUNITID=?, "
                + "FILEPATH=?, "
                + "FIRSTPARENTCHANGEUNITID=?, "
                + "FIRSTPARENTCHANGEUNITITEMID=?, "
                + "SECONDPARENTCHANGEUNITID=?, "
                + "SECONDPARENTCHANGEUNITITEMID=?, "
                + "REFCHANGELOGID=? "
                + " where CHANGEUNITITEMID =? ";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql); 
            
            stmt.setString(1, changeUnitItemDTO.getChangeUnitItemId());
            stmt.setString(2, changeUnitItemDTO.getChangeUnitId());
            stmt.setString(3, changeUnitItemDTO.getFilePath());
            stmt.setString(4, changeUnitItemDTO.getFirstParentChangeUnitId());
            stmt.setString(5, changeUnitItemDTO.getFirstParentChangeUnitItemId());
            stmt.setString(6, changeUnitItemDTO.getSecondParentChangeUnitId());
            stmt.setString(7, changeUnitItemDTO.getSecondParentChangeUnitItemId());
            stmt.setString(8, changeUnitItemDTO.getRefChangeLogId());       
            stmt.setString(9, changeUnitItemDTO.getChangeUnitItemId());
            
            stmt.executeUpdate(); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChangeUnitItemDAO.update: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }     
    }

    @Override
    public void delete(String changeUnitItemId) throws PersistanceException {
        Connection conn = null; 
        Statement stmt = null;  
        
        //Create SQL Statement 
        String sql="delete from APP.CHANGEUNITITEM "  
                   +"WHERE CHANGEUNITITEMID = '"+changeUnitItemId+"'";  
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            stmt.executeUpdate(sql); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChangeUnitItemDAO.delete: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
    }

    @Override
    public List<ChangeUnitItemDTO> findByChangeUnitId(String changeUnitId) throws PersistanceException {
        
        List<ChangeUnitItemDTO> dtoArray = new ArrayList<ChangeUnitItemDTO>();
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select CHANGEUNITITEMID, CHANGEUNITID, FILEPATH, "
                +"FIRSTPARENTCHANGEUNITID, FIRSTPARENTCHANGEUNITITEMID, "
                +"SECONDPARENTCHANGEUNITID, SECONDPARENTCHANGEUNITITEMID, REFCHANGELOGID "
                +" from APP.CHANGEUNITITEM where CHANGEUNITID = '"+changeUnitId+"'";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            while(rs.next()){
                ChangeUnitItemDTO dto = new ChangeUnitItemDTO();
                dto.setChangeUnitItemId(rs.getString("CHANGEUNITITEMID"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));
                dto.setFilePath(rs.getString("FILEPATH"));
                dto.setFirstParentChangeUnitId(rs.getString("FIRSTPARENTCHANGEUNITID"));
                dto.setFirstParentChangeUnitItemId(rs.getString("FIRSTPARENTCHANGEUNITITEMID"));
                dto.setSecondParentChangeUnitId(rs.getString("SECONDPARENTCHANGEUNITID"));
                dto.setSecondParentChangeUnitItemId(rs.getString("SECONDPARENTCHANGEUNITITEMID"));
                dto.setRefChangeLogId(rs.getString("REFCHANGELOGID"));

                dtoArray.add(dto);
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ChangeUnitItemDAO.findByChangeUnitId: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dtoArray; 
    }

    @Override
    public List<ChangeUnitItemDTO> list() throws PersistanceException {
        
        List<ChangeUnitItemDTO> dtoArray = new ArrayList<ChangeUnitItemDTO>();
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select CHANGEUNITITEMID, CHANGEUNITID, FILEPATH, "
                +"FIRSTPARENTCHANGEUNITID, FIRSTPARENTCHANGEUNITITEMID, "
                +"SECONDPARENTCHANGEUNITID, SECONDPARENTCHANGEUNITITEMID, REFCHANGELOGID "
                +" from APP.CHANGEUNITITEM ";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            while(rs.next()){
                ChangeUnitItemDTO dto = new ChangeUnitItemDTO();
                dto.setChangeUnitItemId(rs.getString("CHANGEUNITITEMID"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));
                dto.setFilePath(rs.getString("FILEPATH"));
                dto.setFirstParentChangeUnitId(rs.getString("FIRSTPARENTCHANGEUNITID"));
                dto.setFirstParentChangeUnitItemId(rs.getString("FIRSTPARENTCHANGEUNITITEMID"));
                dto.setSecondParentChangeUnitId(rs.getString("SECONDPARENTCHANGEUNITID"));
                dto.setSecondParentChangeUnitItemId(rs.getString("SECONDPARENTCHANGEUNITITEMID"));
                dto.setRefChangeLogId(rs.getString("REFCHANGELOGID"));

                dtoArray.add(dto);
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ChangeUnitItemDAO.list: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dtoArray; 
    }
}
